var express = require('express');
var querydb = require('../utils/querydb');
var router = express.Router(); //获取路由对象

/* 
 * 添加歌曲
 */
router.post("/add", (req, res) => {
    const title = req.body.title; //接收表单参数
    const artist = req.body.artist;
    const picture = req.body.picture;
    const url = req.body.url;
    const lyric = req.body.lyric;

    const sql = 'INSERT INTO y_song (title,artist,picture,url,lyric) VALUES (?,?,?,?,?)'
    const params = [title, artist, picture, url, lyric];

    querydb(sql, params).then(data => {
        console.log("添加音乐数据成功!");
        res.send({
            resultCode: 1,
            resultInfo: 'ok',
        });
    }).catch(error => {
        res.send({
            resultCode: -1,
            resultInfo: 'no',
        });
    });

})

/* 音乐列表 */
router.get('/list', (req, res) => {
    var pageSize = 5; // 每页记录条数
    var totalNo = 0; // 总记录条数
    var pagesNum = 0; // 总页数
    if (req.query.pageSize) {
        pageSize = req.query.pageSize;
        pageSize = parseInt(pageSize);
    }
    // ===接收参数页号====
    var currentNo = req.query.pageNo; // 当前页号
    if (currentNo == null && typeof (currentNo) === 'undefined') {
        currentNo = 1;
    }
    // console.log('currentNo :' + currentNo, ' pageSize :', pageSize);
    // ==== 总页数

    querydb('SELECT count(*) as num FROM y_song').then(data => {
        totalNo = data[0].num; // 总记录条数
        if (totalNo % pageSize === 0) {
            pagesNum = totalNo / pageSize;
        } else {
            pagesNum = totalNo / pageSize + 1;
        }
        // console.log('总页数 ' + parseInt(pagesNum));

        // ==== 根据偏移量查询分页记录
        var start = (currentNo - 1) * pageSize; // 偏移量

        var sql = "SELECT sid,title,artist,picture,url,lyric FROM y_song ORDER BY sid DESC  LIMIT ?, ? ";
        var param = [start, pageSize];
        return querydb(sql, param);
    }).then(dataTwo => {
        // console.log(JSON.stringify(dataTwo));
        if (dataTwo != null && dataTwo.length > 0) {
            res.send({
                resultCode: 1,
                resultInfo: {
                    total: totalNo,
                    currentNo: parseInt(currentNo),
                    pageSize: parseInt(pageSize),
                    totalNum: parseInt(pagesNum),
                    list: dataTwo
                },
            })
        } else {
            res.send({
                resultCode: -1,
                resultInfo: '没有数据',
            });
        }
    })
})

/* 修改音乐 /api/song/update */
router.post("/update", function (req, res) {
    const jobSQL = (sql)=>{
        const str = sql.substring(sql.length-1);
        if(str === '?'){
            return ',';
        }else{
            return '';
        }
    } 

    const sid = req.body.sid;
    const title = req.body.title; //接收表单参数
    const artist = req.body.artist;
    const picture = req.body.picture;
    const url = req.body.url;
    const lyric = req.body.lyric;

    // var sql = "UPDATE  y_song  SET title = ? ,artist = ?,picture = ?,url = ?,lyric = ? WHERE sid = ?";
    let sql = "UPDATE  y_song  SET ";
    console.log('title ',title);
    if(title){
        sql = sql + " title = ?"
    }
    if(artist){
        sql = sql + jobSQL(sql) + " artist = ?"
    }
    if(picture){
        sql = sql + jobSQL(sql) + " picture = ?"
    }
    if(url){
        sql = sql + jobSQL(sql) + " url = ?"
    }
    if(lyric && lyric != ''){
        sql = sql + jobSQL(sql) + " lyric = ?"
    }
    sql = sql + " WHERE sid = ?"

    console.log('sql >> ',sql);

    let parameters = []
    if(title){
        parameters.push(title);
    }
    if(artist){
        parameters.push(artist);
    }
    if(picture){
        parameters.push(picture);
    }
    if(url){
        parameters.push(url);
    }
    if(lyric){
        parameters.push(lyric);
    }
    parameters.push(sid);

    console.log('parameters ',parameters);

    querydb(sql, parameters).then(data => {
        // console.log("修改数据成功!" + JSON.stringify(data));
        res.send({
            resultCode: 1,
            resultInfo: 'ok',
        })
    })
})

/* 删除音乐 */
router.get('/delete', (req, res, next) => {
    const sid = req.query.sid;
    const sql = "DELETE FROM y_song WHERE sid = ?";
    const params = [sid];
    querydb(sql, params).then(data => {
        res.send({
            resultCode: 1,
            resultInfo: 'ok'
        })
    })
})

/**
 * 查询音乐
 * parms: id
 */
router.get("/find", function (req, res, next) {
    var sid = req.query.sid; //接收表单参数
    if (!sid) {
        res.send({
            resultCode: -1,
            resultInfo: '参数出错',
        })
        return
    }

    var sql = "SELECT sid,title,artist,picture,url,lyric FROM y_song WHERE sid = ?";
    var parameters = [sid];

    querydb(sql, parameters).then(function (data) {
        res.send({
            resultCode: 1,
            resultInfo: data[0],
        });
    }).catch(next);

});


module.exports = router;